package com.info.zhiduoduo.repository.mapper.crowdsource;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.baomidou.mybatisplus.mapper.Condition;
import com.info.zhiduoduo.repository.dto.account.MonthCrowdSourceTradingVolumeDto;
import com.info.zhiduoduo.repository.dto.account.ReceivableDto;
import com.info.zhiduoduo.repository.dto.company.UrgeUploadProjectFileDto;
import com.info.zhiduoduo.repository.dto.crowdsource.*;
import com.info.zhiduoduo.repository.dto.invoice.CostInvoiceListDto;
import com.info.zhiduoduo.repository.dto.invoice.InvoiceBriefDto;
import com.info.zhiduoduo.repository.dto.invoice.MergeCostInvoiceListDto;
import com.info.zhiduoduo.repository.dto.project.ProjectContractDto;
import com.info.zhiduoduo.repository.dto.statistics.StatisticLastThreeMonthsDto;
import com.info.zhiduoduo.repository.entity.company.CrowdsourceMonthlyDepositRecord;
import com.info.zhiduoduo.repository.entity.crowdsource.CrowdsourceInvoice;
import com.info.zhiduoduo.repository.entity.crowdsource.CrowdsourceProject;
import com.info.zhiduoduo.repository.entity.invoice.Invoice;
import com.info.zhiduoduo.repository.entity.partner.PartnerCompanyMonthlyCrowdCost;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * <p>
 * 智众包项目 Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2019-09-25
 */
public interface CrowdsourceProjectMapper extends BaseMapper<CrowdsourceProject> {

	default List<CrowdsourceProject> listByCompid(Integer compid) {
		return new CrowdsourceProject().selectList(Condition.create().eq("compid", compid).eq("state", 4));
	}

	@Select(" select csp.*,sm.managername as supplementPersonnel,ccp.company_name as partyname,GROUP_CONCAT(item.user_type) as usertypes,pay.pay_name " +
			" from zdd_crowdsource_project csp " +
			" left join zdd_crowdsource_project_item item on csp.projectid = item.projectid" +
			" left join zdd_payment pay on csp.pay_id = pay.id " +
			" left join zdd_company c on c.compid = csp.compid " +
			" left join zdd_system_manager sm on sm.managerid = csp.supplement_personnel " +
			" LEFT JOIN zdd_crowdsource_company_party ccp ON ccp.id = csp.party_id" +
			" LEFT JOIN zdd_crowdsource_trade_record ctr ON	ctr.projectid = csp.projectid " +
			" where CONCAT(csp.compname,csp.project_name) like '%${name}%' " +
			" and CASE" +
			"		when #{createType} = 0 then csp.state in (2,3,4) " +
			"		when #{createType} = 1 then csp.state in (2,3,4) and csp.create_type = 1 " +
			" 		when #{createType} = 2 then csp.state in (1,2,3,4) and csp.create_type = 2 and (csp.create_by = #{createBy} or 4 = #{roleId} ) " +
			"	  END " +
			" and csp.createtime between #{startDate} and #{endDate} " +
			" and IF(#{projectType}=0, 1=1, csp.project_type = #{projectType}) " +
			" and IF(#{workType}=0, 1=1, csp.work_type = #{workType}) " +
			" and IF(#{whetherSupplement}=1, csp.supplement = 1, 1=1) " +
			" and IF(#{whetherSupplement}=2, csp.supplement = 0, 1=1) " +
			" and IF(#{partyId}=0, 1=1, csp.party_id = #{partyId})" +
			" and csp.deleted = 0 " +
			" ${strUploadFile} " +
			" GROUP BY csp.projectid" +
			" HAVING if(#{filterUserType}=0, 1=1 ,position(#{filterUserType} in usertypes)=0)" +
			" order by csp.state ,csp.createtime DESC ")
	List<CrowdsourceProjectBriefDto> listEffectiveProject(String name, int projectType,int state, Date startDate, Date endDate,String strUploadFile,
														  int whetherSupplement, Integer createType,Integer createBy,Integer roleId, int partyId, int filterUserType,int workType);

	@Select(" select csp.*,pay.pay_name from zdd_crowdsource_project csp " +
			" left join zdd_company c on c.compid = csp.compid " +
			" left join zdd_payment pay on csp.pay_id = pay.id " +
			" where csp.compid = #{compid} " +
			" and csp.project_name like '%${name}%' " +
			" and if(#{state} = 0, 1 = 1, csp.state = #{state}) " +
			" and if(ISNULL(#{workType}) or #{workType} = 0, 1 = 1, csp.work_type = #{workType}) " +
			" and if(#{whetherUploadFile} = 0, 1 = 1, " +
			"	   if(#{whetherUploadFile} = 1,(csp.project_file_url is not null or csp.delivery_path is not null)," +
			"         (csp.project_file_url is null and csp.delivery_path is null)" +
			"      ) " +
			"     ) " +
			" and csp.createtime between #{startDate} and #{endDate} " +
			" and csp.project_type = 1 " +
			" and csp.create_type = 1 " +
			" and csp.deleted = 0 " +
			" order by csp.state ASC,csp.projectid DESC")
	List<CrowdsourceProjectBriefDto> getListByCompid(int compid, String name, int state, Date startDate, Date endDate,Integer workType,int whetherUploadFile);

	@Select(" select csp.compid,csp.compname,c.base_tax_rate,c.deposit_flag,c.deposit_rate,c.return_period,c.secondary_monthly_amount,c.secondary_deposit_rate," +
			" DATE_FORMAT(csp.finishtime, '%Y-%m-01') as 'month',IFNULL(SUM(csp.project_total_amount), 0) as projectAmount," +
			" IFNULL(SUM(csp.deposit_total_amount), 0) as shouldReturnDeposit,0 as isOnline," +
			" IFNULL(SUM(csp.project_total_service_charge), 0) as projectTaxFee  " +
			" from zdd_crowdsource_project csp " +
			" join zdd_company c on c.compid = csp.compid " +
			" where csp.state in(4,5) " +
			" and csp.deposit_rate != 0" +
			" and csp.finishtime between #{startDate} and #{endDate} " +
			" and csp.project_type = 1 " +
			" and csp.deleted = 0 " +
			" group by compid ")
	List<CrowdsourceMonthlyDepositRecord> sumTotalProjectAmountGroupByCompid(Date startDate, Date endDate);

	@Update(" update zdd_crowdsource_project set deposit_back_status = 1 where compid = #{compId} and finishtime BETWEEN #{startTime} AND #{endTime} ")
	Integer updateProjectDepositState(Integer compId,Date startTime,Date endTime);

	@Select(" select csp.*,i.starttime,i.finishtime  AS finishtimes,pay.pay_name " +
			" from zdd_crowdsource_project csp " +
			" left join zdd_payment pay on csp.pay_id = pay.id " +
			" LEFT JOIN zdd_invoice i ON i.projectid = csp.projectid AND i.project_type = 2 " +
			" where csp.projectid = #{projectid} ")
	CrowdsourceProjectDetailDto getDetail(int projectid);

	@Select(" select csp.projectid,csp.project_name as projectname,csp.invoice_content as content," +
			" csp.project_total_payable_amount as invoiceAmount, 0 as type,2 as projectType,2 as invoicetype," +
			" csp.compid,c.creditcode,csp.updatetime as projectfinishtime " +
			" from zdd_crowdsource_project csp " +
			" join zdd_company c on c.compid = csp.compid " +
			" where csp.state = 4 " +
			" and csp.createtime between #{startDate} and #{endDate} " +
			" and concat(csp.project_name,csp.compname) like '%${name}%' " +
			" and not exists(select 1 from zdd_invoice i where i.projectid = csp.projectid) " +
			" and csp.deleted = 0 " +
			" order by csp.projectid desc ")
	List<InvoiceBriefDto> getPendingInvoiceProjectList(String name, Date startDate, Date endDate);

	@Select(" select csp.projectid,csp.project_name as projectname,csp.invoice_content as content," +
			" csp.project_total_payable_amount as invoiceAmount,0 as type,c.invoice_type as invoicetype," +
			" csp.compid,csp.compname,c.creditcode as compTaxno,c.acctbank as compAcctbank,csp.actual_sender as actual_company_id," +
			" c.acctno as compAcctno,c.telephone as compTelephone,IFNULL(c.invoiceaddress,c.contactaddress) as compAddress," +
			" c.compemail as email,c.contactname,c.contactphone,csp.pay_id,csp.party_id,csp.crowdsource_city as projectCityId " +
			" from zdd_crowdsource_project csp " +
			" left join zdd_company c on c.compid = csp.compid " +
			" where csp.projectid = #{projectid}")
	Invoice getPendingInvoiceDetail(int projectid);

	@Select(" select IFNULL(SUM(project_total_amount),0) " +
			" from zdd_crowdsource_project csp " +
			" where if(ISNULL(#{compids}) or #{compids} = '', 1=0, csp.compid in (${compids})) " +
			" and state in(4,5) " +
			" and YEAR(createtime) = YEAR(#{month}) " +
			" and QUARTER(createtime) = QUARTER(#{month}) ")
	BigDecimal getQuarterTotalAmountByCompids(String compids, Date month);

	@Select(" select IFNULL(SUM(project_total_amount), 0) as projectTotalAmount," +
			" IFNULL(SUM(project_total_service_charge), 0) as projectTotalTaxfee " +
			" from zdd_crowdsource_project csp " +
			" join zdd_company c on c.compid = csp.compid " +
			" where if(ISNULL(#{compid}), 1=1, csp.compid = #{compid}) " +
			" and if(ISNULL(#{crowdsourcePartnerIds}) or #{crowdsourcePartnerIds} = '', 1=0, c.crowdsource_partner_id in (${crowdsourcePartnerIds})) " +
			" and csp.state in(4,5) " +
			" and YEAR(csp.finishtime) = YEAR(#{month}) " +
			" and MONTH(csp.finishtime) = MONTH(#{month}) " +
			" and project_type = 1 " +
			" and csp.deleted = 0 ")
	CrowdsourceProjectSumDto sumMonthTotalAmountByCrowdsourcePartner(String crowdsourcePartnerIds, Integer compid, Date month);

	@Select(" select c.crowdsource_partner_id as partnerid,csp.compid,c.deposit_flag,c.base_tax_rate,c.deposit_rate,c.return_period,c.secondary_monthly_amount,c.secondary_deposit_rate," +
			" DATE_FORMAT(csp.finishtime, '%Y-%m-01') as 'month',IFNULL(SUM(csp.project_total_amount), 0) as projectAmount," +
			" IFNULL(SUM(csp.project_total_service_charge), 0) as projectTaxFee  " +
			" from zdd_crowdsource_project csp " +
			" join zdd_company c on c.compid = csp.compid " +
			" where csp.state in(4,5) " +
			" and if(ISNULL(#{crowdsourcePartnerIds}) or #{crowdsourcePartnerIds} = '', 1=0, c.crowdsource_partner_id in (${crowdsourcePartnerIds})) " +
			" and YEAR(csp.finishtime) = YEAR(#{month}) " +
			" and MONTH(csp.finishtime) = MONTH(#{month}) " +
			" and csp.project_type = 1 " +
			" and csp.deleted = 0 " +
			" group by c.crowdsource_partner_id,compid ")
	List<PartnerCompanyMonthlyCrowdCost> listProjectCostGroupByPartnerCompid(String crowdsourcePartnerIds, Date month);

	@Select(" select DISTINCT csp.compid " +
			" from zdd_crowdsource_project csp " +
			" where if(ISNULL(#{compids}) or #{compids} = '', 1=0, csp.compid in (${compids})) " +
			" and state in(4,5) " +
			" and YEAR(createtime) = YEAR(#{month}) " +
			" and QUARTER(createtime) = QUARTER(#{month}) ")
	List<Integer> getTradeCompanyByCompids(String compids, Date month);

	@Select(" select COUNT(projectid) from zdd_crowdsource_project " +
			" where compid = #{compid} " +
			" and state not in(4,5,98,99) " +
			" and deleted != 1")
	int countUnfinishProject(int compid);

	@Select(" select cp.compid,cp.compname,cp.projectid,cp.project_name,cp.project_total_amount,cp.project_total_service_charge,cp" +
			".people_count," +
			" cp.state as projectState,cp.createtime " +
			" from zdd_crowdsource_project cp " +
			" join zdd_company c on c.compid = cp.compid " +
			" where cp.compid = #{compid} " +
			" and CONCAT(cp.project_name) like '%${name}%' " +
			" and cp.state < 4 " +
			" and IF(ISNULL(#{startDate}),1=1, cp.createtime >= #{startDate}) "  +
			" and IF(ISNULL(#{endDate}),1=1, cp.createtime <= #{endDate}) " +
			" and cp.project_type = 2 " +
			" and cp.deleted = 0 ")
	List<CrowdsourceProjectMatchDto> listprojectUnmatch(@Param("compid") int compid, @Param("name") String name, @Param("startDate") Date startDate, @Param("endDate") Date endDate);

	@Select(" SELECT cp.createtime, cp.compname, ctr.batch_no, cp.project_name, cp.project_total_amount, " +
			" cp.project_total_service_charge, cp.project_total_payable_amount, ctr.total_count, " +
			" cp.party_id,(SELECT company_name from zdd_crowdsource_company_party where id = cp.party_id)as partyName, " +
			" IF( cp.state < 3, 0, ctr.total_fail_count ) AS totalFailCount, " +
			" IF( cp.state < 3, 0, ctr.total_succ_count ) AS totalSuccCount, " +
			" IF( cp.state < 3, 0, ctr.total_fail_amount ) AS totalFailAmount, " +
			" IF( cp.state < 3, 0, ctr.total_succ_amount ) AS totalSuccAmount, " +
			" cp.state, cp.projectid FROM zdd_crowdsource_project cp " +
			" LEFT JOIN zdd_crowdsource_trade_record ctr ON ctr.projectid = cp.projectid " +
			" LEFT JOIN zdd_company c ON c.compid = cp.compid " +
			" WHERE cp.pay_id = 4 AND cp.createtime BETWEEN #{startDate} AND #{endDate} " +
			" AND IF(ISNULL(#{companyName}) OR #{companyName} = '', 1=1, cp.compname like '%${companyName}%') " +
			" AND IF(#{state} = 0, 1=1, cp.state = #{state}) AND cp.deleted = 0 " +
			" ORDER BY cp.createtime DESC ")
	List<CrowdOfflinePayProjectListDto> queryOfflinePayProjectByCondition(@Param("startDate") Date startDate, @Param("endDate") Date endDate,
																		  @Param("companyName") String companyName, @Param("state") int state);

	/**
	 * 该创客目前参与的 未付款的众包项目个数（除去已删除的项目）
	 * @param userid 创客userid
	 * @return int
	 */
	@Select(" SELECT count( * )  FROM zdd_crowdsource_project cp " +
			" LEFT JOIN zdd_crowdsource_project_item cpi ON cpi.projectid = cp.projectid " +
			" WHERE " +
			" cp.deleted = 0  AND cpi.userid = #{userid} " +
			" AND cpi.user_type = 2 AND cp.state NOT IN ( 4, 99,1 )")
	int notCompleteCrowdSourceProjectCountByUserid(@Param("userid") int userid);

	@Select("delete csp.*,cspi.*,cstr.*,cstri.* " +
			"from zdd_crowdsource_project csp " +
			"left join zdd_crowdsource_project_item cspi on cspi.projectid = csp.projectid " +
			"left join zdd_crowdsource_trade_record cstr on cstr.projectid = csp.projectid " +
			"left join zdd_crowdsource_trade_record_item cstri on cstri.projectid = csp.projectid " +
			"where csp.compid = #{compid} and cspi.userid = #{userid} and cspi.user_type = #{userType}")
	void delProjectByCompidAndUserid(int compid, int userid, int userType);


	/**
	 * 查询需要催促上传众包任务证据链的项目信息 对应企业信息  对应商务经理信息
	 * 在给企业发送催促上传证据链消息时候用到
	 * @return
	 */
	@Select(" SELECT cp.projectid,cp.project_name,cp.compid,cp.compname,cp.createtime, " +
			" ctr.finishtime,acct.business_manager_id,bm.business_manager,company.openid as companyOpenid " +
			" FROM zdd_crowdsource_project cp LEFT JOIN zdd_crowdsource_trade_record ctr ON ctr.projectid = cp.projectid " +
			" LEFT JOIN zdd_company company ON company.compid = cp.compid " +
			" LEFT JOIN zdd_company_account acct ON acct.compid = cp.compid " +
			" LEFT JOIN zdd_business_manager bm ON bm.business_manager_id = acct.business_manager_id " +
			" WHERE cp.deleted = 0 AND ctr.deleted = 0 " +
			" AND cp.state = 4 AND cp.urge_upload_project_file_count < 3 " +
			" AND cp.project_file_url IS NULL AND YEAR ( cp.createtime ) > '2020' ")
	List<UrgeUploadProjectFileDto> urgeUploadProjectFileToCompany();

	/**
	 * 查询需要催促上传众包任务证据链的项目信息 对应企业信息  对应商务经理信息
	 * 在给胡总 发送需要催促企业上传证据链消息时候 用到
	 * @return
	 */
	@Select(" SELECT cp.projectid,cp.project_name,cp.compid,cp.compname,cp.createtime, " +
			" ctr.finishtime,acct.business_manager_id,bm.business_manager,company.openid as companyOpenid " +
			" FROM zdd_crowdsource_project cp LEFT JOIN zdd_crowdsource_trade_record ctr ON ctr.projectid = cp.projectid " +
			" LEFT JOIN zdd_company company ON company.compid = cp.compid " +
			" LEFT JOIN zdd_company_account acct ON acct.compid = cp.compid " +
			" LEFT JOIN zdd_business_manager bm ON bm.business_manager_id = acct.business_manager_id " +
			" WHERE cp.deleted = 0 AND ctr.deleted = 0 " +
			" AND cp.state = 4 AND cp.urge_upload_project_file_count >= 3 " +
			" AND cp.project_file_url IS NULL AND YEAR ( cp.createtime ) > '2020' ")
	List<UrgeUploadProjectFileDto> urgeUploadProjectFileToBoss();

	/**
	 * 查询需要催促上传众包任务证据链的项目信息 对应企业信息  对应商务经理信息
	 * 在给商务经理 发送催促上传证据链消息时候 用到
	 * @return
	 */
	@Select(" SELECT cp.projectid,cp.project_name,cp.compid,cp.compname,cp.createtime, " +
			" ctr.finishtime,acct.business_manager_id,bm.business_manager,company.openid as companyOpenid " +
			" FROM zdd_crowdsource_project cp LEFT JOIN zdd_crowdsource_trade_record ctr ON ctr.projectid = cp.projectid " +
			" LEFT JOIN zdd_company company ON company.compid = cp.compid " +
			" LEFT JOIN zdd_company_account acct ON acct.compid = cp.compid " +
			" LEFT JOIN zdd_business_manager bm ON bm.business_manager_id = acct.business_manager_id " +
			" WHERE cp.deleted = 0 AND ctr.deleted = 0 " +
			" AND cp.state = 4 " +
			" AND cp.project_file_url IS NULL AND YEAR ( cp.createtime ) > '2020' ")
	List<UrgeUploadProjectFileDto> urgeUploadProjectFileToBusinessManager();

	@Select(" SELECT " +
			" DATE_FORMAT( cp.createtime, '%Y' ) AS years," +
			" DATE_FORMAT( cp.createtime, '%m' ) AS months," +
			" IFNULL( sum( CASE cp.party_id WHEN 1 THEN cp.project_total_amount END ), 0 ) AS jiuGeProjectamount, " +
			" IFNULL( sum( CASE cp.party_id WHEN 1 THEN cp.project_total_service_charge END ), 0 ) AS jiuGeProjectservicecharge, " +
			" IFNULL( sum( CASE cp.party_id WHEN 2 THEN cp.project_total_amount END ), 0 ) AS wuShengProjectamount, " +
			" IFNULL( sum( CASE cp.party_id WHEN 2 THEN cp.project_total_service_charge END ), 0 ) AS wuShengProjectservicecharge, " +
			" IFNULL( sum( CASE cp.party_id WHEN 3 THEN cp.project_total_amount END ), 0 ) AS zhiShengProjectamount, " +
			" IFNULL( sum( CASE cp.party_id WHEN 3 THEN cp.project_total_service_charge END ), 0 ) AS zhiProjectservicecharge, " +
			" IFNULL( sum( CASE cp.party_id WHEN 4 THEN cp.project_total_amount END ), 0 ) AS xinShengProjectamount, " +
			" IFNULL( sum( CASE cp.party_id WHEN 4 THEN cp.project_total_service_charge END ), 0 ) AS xinShengProjectservicecharge " +
			" FROM " +
			" zdd_crowdsource_project cp " +
			// " LEFT JOIN zdd_company c ON c.compid = cp.compid " +
			" WHERE cp.state = 4 " +
			" AND cp.deleted = 0  " +
			" AND YEAR ( cp.finishtime ) = #{years} " +
			" GROUP BY months")
	List<MonthCrowdSourceTradingVolumeDto> getCrowdSourceTradingVolumeList(int years);


	/**
	 * 成本发票-合并开票列表
	 * @param name name
	 * @param startDate startDate
	 * @param endDate endDate
	 * @param state state
	 * @return MergeCostInvoiceListDto
	 */
	@Select(" select id,project_amount,state,entrusted_operator,project_service_name, " +
			" createtime,whether_merge,type,merge_remarks,enclosure_info,invoice_city_id " +
			" FROM zdd_crowdsource_invoice " +
			" where project_service_name like '%${name}%' " +
			" AND if(#{state} != 0, state = #{state}, 1 = 1)" +
			" AND createtime between #{startDate} and #{endDate} " +
			" AND whether_merge = 1")
	List<MergeCostInvoiceListDto> mergeCostInvoiceList(String name, Date startDate, Date endDate, int state);

	/**
	 * 查看关联的所有单条成本发票信息
	 * @param id 合并生成的发票信息主键id
	 * @return CostInvoiceListDto
	 */
	@Select(" SELECT i.id,i.project_amount,i.createtime,i.project_service_name,i.state,i.projectid, " +
			" i.whether_merge,i.merge_id," +
			" cp.project_name,cp.createtime as projectCreateTime,ca.realname,ca.idno,c.phone " +
			" FROM zdd_crowdsource_invoice i " +
			" LEFT JOIN zdd_crowdsource c ON c.crowdsourceid = i.crowdsourceid" +
			" LEFT JOIN zdd_crowdsource_account ca ON ca.crowdsourceid = i.crowdsourceid " +
			" LEFT JOIN zdd_crowdsource_project cp ON cp.projectid = i.projectid " +
			" WHERE i.merge_id = #{id} ")
	List<CostInvoiceListDto> queryAllRelationCostInvoice(@Param("id") int id);

	/**
	 * 查询出所有未开票并且未压缩过自然人身份证文件的合并开票
	 * @return
	 */
	@Select(" SELECT * FROM zdd_crowdsource_invoice WHERE state = 2 AND (enclosure_info IS NULL OR enclosure_info = '') AND whether_merge = 1")
	List<CrowdsourceInvoice> queryNoCompressedInvoice();

	/**
	 * 根据merge_id查询出被关联的数据
	 * @param mergeId 关联的发票id
	 * @return List<CrowdsourceInvoice>
	 */
	@Select(" SELECT * FROM zdd_crowdsource_invoice WHERE merge_id = #{mergeId}")
	List<CrowdsourceInvoice> queryBeMergeCostInvoice(int mergeId);

	@Select(" SELECT cp.createtime, c.kingdee_customer_code AS releaseCompanyCode, " +
			" cp.compname AS releaseCompanyName, ccp.organization_code AS subjectCompanyOrganizationCode, " +
			" ccp.company_name AS subjectCompanyName, bm.salesperson_code AS businessManagerCode, " +
			" bm.nickname AS businessManagerName, cpi.project_service_charge AS projectTaxAmount, " +
			" cpi.project_amount, cpi.integrated_tax_fee AS transactionAmount, ccp.materiel_code, " +
			" c.base_tax_rate AS projectBaseServiceRate " +
			" FROM zdd_crowdsource_project_item cpi " +
			" LEFT JOIN zdd_crowdsource_project cp ON cp.projectid = cpi.projectid " +
			" LEFT JOIN zdd_company c ON c.compid = cp.compid " +
			" LEFT JOIN zdd_company_account ca ON ca.compid = c.compid " +
			" LEFT JOIN zdd_crowdsource_company_party ccp ON ccp.id = cp.party_id " +
			" LEFT JOIN zdd_business_manager bm ON bm.business_manager_id = ca.business_manager_id " +
			" WHERE cp.state = 4 AND cp.deleted = 0 " +
			" AND DATE(cp.finishtime) BETWEEN DATE(#{startDate}) AND DATE(#{endDate}) ")
	List<ReceivableDto> queryReceivableDto(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

	/**
	 * 查询智众包企业N个月内的交易时段和交易额
	 * @param crowdsourceName 众包企业名
	 * @param n n个月
	 * @return
	 */
	@Select("SELECT zcp.compname as name,MIN(DAY(zcp.finishtime)) start,MAX(DAY(zcp.finishtime)) end,SUM(zcp" +
			".project_total_payable_amount) gmv " +
			"FROM zdd_crowdsource_project zcp LEFT JOIN zdd_company zc ON zcp.compid = zc.compid " +
			"WHERE DATE(zcp.finishtime) BETWEEN DATE_ADD(DATE_SUB(CURDATE(), INTERVAL #{n} MONTH),interval -day(curdate())+1 day) AND LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) " +
			"AND zcp.deleted = 0 " +
			"AND zcp.state BETWEEN 3 AND 4 "+
			"AND zcp.compname LIKE CONCAT(\"%\",IFNULL(#{crowdsourceName},\"\"),\"%\") " +
			"GROUP BY zcp.compid")
	List<StatisticLastThreeMonthsDto> getCrowdsourceStatisticLastNMonths(@Param("crowdsourceName") String crowdsourceName,@Param("n")Integer n);

	/**
	 * 查询企业下所有已完成的众包项目
	 * @param compid
	 * @return
	 */
	@Select(" select csp.projectid,csp.project_name as projectname,csp.createtime as publishTime,csp.compid,csp.compname,csp.delivery_path," +
			" csp.finishtime as projectfinish,ccp.company_name as partyname,round(IFNULL(csp.project_total_invoice_amount,0),2) as projectAmount,csp.work_type " +
			" from zdd_crowdsource_project_item cspi  " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) " +
			" join zdd_crowdsource_trade_record_item cstri on cstri.project_item_id = cspi.id and repay = 0 and cstri.status in (${states})" +
			" left join zdd_company com on csp.compid = com.compid " +
			" left join zdd_crowdsource_company_party ccp on csp.party_id = ccp.id " +
			" where IF(ISNULL(#{compid}) or #{compid} = 0, 1=1, csp.compid = #{compid})" +
			" and IF(ISNULL(#{projectIds}) or #{projectIds} = '', 1=1, csp.projectid in(${projectIds})) " +
			" and cspi.deleted = 0" +
			" group by csp.projectid")
	List<ExportProjectAcceptanceDto> getCrowdsourceProjectAcceptance(Integer compid,String projectIds,String states);

	//查询智众包生成合同需要的数据
	@Select(" SELECT item.id,item.projectid,item.userid,item.user_type,item.realname as username,case item.user_type when 2 then item.username else item.realname end as studioName," +
			" round(item.project_service_charge,2) as projectServiceCharge,round(item.project_amount,2) as projectAmount,pro.project_name,pro.project_desc,pro.finishtime," +
			" pro.createtime as projectCreateTime,c.compname,c.contactphone as companyPhone,ccp.company_name as partyName," +
			" CONCAT('ZZB',DATE_FORMAT(pro.createtime,'%Y%m%d'),item.projectid) as contractNo,pro.work_type,ccp.realname," +
			" ccp.addressee_phone,ccp.company_address,zca.idno,zc.phone,zca.province,zca.city,zca.acctno,zca.bankname " +
			" from zdd_crowdsource_project_item item " +
			" left join zdd_crowdsource_project pro on item.projectid = pro.projectid" +
			" left join zdd_company c on pro.compid=c.compid" +
			" left join zdd_company_account ca on ca.compid = pro.compid" +
			" left join zdd_crowdsource_company_party ccp on ccp.id = pro.party_id" +
			" left join zdd_crowdsource_account zca on item.userid = zca.crowdsourceid " +
			" left join zdd_crowdsource zc on item.userid = zc.crowdsourceid " +
			" where item.id = #{projectItemId}")
	CrowdsourceContractDto getProjectContractInfo(Integer projectItemId);

	@Select(" select SUM(csp.project_total_amount) AS projectTotalAmount," +
			" SUM(csp.project_total_service_charge) AS projectTotalServiceCharge, " +
			" SUM(csp.deposit_total_amount) as depositTotalAmount " +
			" from zdd_crowdsource_project csp " +
			" left join zdd_company c on c.compid = csp.compid " +
			" where csp.compid = #{compid} " +
			" and csp.project_name like '%${name}%' " +
			" and if(#{state} = 0, 1 = 1, csp.state = #{state}) " +
			" and csp.createtime between #{startDate} and #{endDate} " +
			" and csp.project_type = 1 " +
			" and if(#{workType} = 0, 1 = 1, csp.work_type = #{workType}) " +
			" and csp.deleted = 0 " )
	CrowdsourceAmountsDto crowdsourcingTasksCountAmounts(int compid,
														 String name,
														 int state,
														 Date startDate,
														 Date endDate,
														 Integer workType);

	// 查询企业已完成超过5天未提交验收单的项目Id，过滤掉后台代发项目
	@Select(" select cp.projectid " +
			" from zdd_crowdsource_project cp " +
			" where cp.deleted = 0" +
			" and cp.compid = #{compid} " +
			" and cp.state = 4  " +
			" and cp.delivery_path is null AND cp.finishtime <= DATE_SUB(CURRENT_DATE(),INTERVAL 5 DAY) " +
			" and cp.work_type = #{workType} " +
			" and cp.create_type = 1 ")
	List<String> getCompProjectDeliveryNum(@Param("compid") Integer compid,@Param("workType") Integer workType);

	// 查询企业已完成超过5天未提交验收单的项目Id，只查询后台代发项目
	@Select(" select cp.projectid " +
			" from zdd_crowdsource_project cp " +
			" where cp.deleted = 0" +
			" and cp.state = 4  " +
			" AND cp.delivery_path is null AND cp.finishtime <= DATE_SUB(CURRENT_DATE(),INTERVAL 5 DAY) " +
			" and cp.create_type = 2 ")
	List<String> getCompProjectDeliveryNumType2();

	@Select(" select count(*) " +
			" from zdd_crowdsource_project cp " +
			" where cp.deleted = 0" +
			" and cp.compid = #{compid} " +
			" and cp.state = 4  " +
			" ${str} ")
	int getCroProjectNum(@Param("compid") int compid,
						 @Param("str") String str);

	@Select(" select count(*) " +
			" from zdd_crowdsource_project cp " +
			" where cp.deleted = 0" +
			" and cp.create_type = 2 " +
			" and cp.state = 1 ")
	int getAdminImportCroProjectNum();


}
